﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
namespace Utilities
{
    public class SqlHelper
    {

        #region .      公共函数      .


        /// <summary>
        /// 根据自定义连接字符串获得连接
        /// </summary>
        /// <param name="ConnectionString">链接字符串</param>
        /// <returns></returns>
        public static MySqlConnection GetConnection()
        {
            MySqlConnection Conn = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString);
            Conn.Open();
            return Conn;
        }

        /// <summary>
        /// 抛出异常
        /// </summary>
        /// <param name="e">错误对象</param>
        /// <param name="SQLText">SQL语句</param>
        /// <returns>Exception</returns>
        protected static Exception ThrowSQLError(Exception e, string SQLText)
        {
            return new Exception(e.Message + "<br />SQL语句 ： " + SQLText);
        }
        /// <summary>
        /// 返回DataSet记录集,用户分页
        /// </summary>
        /// <param name="sqlstr">sqlstr语句</param>
        /// <param name="Paged">当前页数</param>
        /// <param name="PageSize">每页显示项数</param>
        /// <returns>DataSet</returns>
        public static DataSet getDataSet(string sqlstr, int Paged, int PageSize)
        {
            using (MySqlConnection Conn = GetConnection())
            {
                MySqlDataAdapter sqlada = new MySqlDataAdapter(sqlstr, Conn);
                DataSet dtset = new DataSet();
                sqlada.Fill(dtset, (Paged) * PageSize, PageSize, "tb");
                return dtset;
            }

        }
        /// <summary>
        /// 为MySqlCommand循环添加参数
        /// </summary>
        /// <param name="cmd">MySqlCommand对象参数</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>MySqlCommand命令对象</returns>
        protected static MySqlCommand LoadParmaters(MySqlCommand cmd, MySqlParameter[] parameters)
        {
            if (parameters == null)
                return cmd;
            foreach (MySqlParameter parameter in parameters)
            {
                //检查未分配值的输出参数,将其分配为DBNull.Value
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input && parameter.Value == null)
                {
                    parameter.Value = DBNull.Value;
                }

                cmd.Parameters.Add(parameter);
            }
            return cmd;
        }
        /// <summary>
        /// 为MySqlCommand循环添加参数
        /// </summary>
        /// <param name="cmd">MySqlCommand对象参数</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>MySqlCommand命令对象</returns>
        protected static MySqlCommand LoadParmaters(MySqlCommand cmd, IDataParameter[] parameters)
        {
            foreach (MySqlParameter parameter in parameters)
            {
                //检查未分配值的输出参数,将其分配为DBNull.Value
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input && Convert.ToString(parameter.Value) == "")
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
            return cmd;
        }
        #endregion

        /// <summary>
        /// 执行SQL语句，并返回影响行数
        /// </summary>
        /// <param name="SQLText"></param>
        /// <param name="parameters">SqlParmeter参数数组</param>
        /// <returns>影响行数</returns>
        public static int ExecuteNonQuery(string SQLText, MySqlParameter[] parameters)
        {
            try
            {
                using (MySqlConnection Conn = GetConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(SQLText, Conn);
                    cmd = LoadParmaters(cmd, parameters);
                    int RowCount = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    Conn.Close();
                    return RowCount;
                }
            }
            catch (Exception Error)
            {
                throw ThrowSQLError(Error, SQLText);
            }
        }

        /// <summary>
        /// 根据查找语句返回行数
        /// </summary>
        /// <param name="SQLText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int GetCount(string SQLText, MySqlParameter[] parameters)
        {
            try
            {
                string str = ExecuteScalar(SQLText, parameters);
                return int.Parse(string.IsNullOrEmpty(str) ? "0" : str);
            }
            catch (Exception Error)
            {
                throw ThrowSQLError(Error, SQLText);
            }
        }
        ///<summary>
        ///执行Sql语句,返回第一行第一列的值
        ///</summary>
        ///<param name="strSql">SQL语句</param>
        ///<returns>String</returns>
        public static string ExecuteScalar(string SQLText, MySqlParameter[] parameters)
        {
            try
            {
                using (MySqlConnection Conn = GetConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(SQLText, Conn);
                    cmd = LoadParmaters(cmd, parameters);
                    string OneOneValue = Convert.ToString(cmd.ExecuteScalar());
                    cmd.Dispose();
                    Conn.Close();
                    return OneOneValue;
                }
            }
            catch (Exception Error)
            {
                throw ThrowSQLError(Error, SQLText);
            }
        }


        #region .      存储过程      .
        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>int</returns>
        public static int RunProcedureCount(string storedProcName, IDataParameter[] parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(storedProcName, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd = LoadParmaters(cmd, parameters);
                int retCount = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                parameters = null;
                return retCount;
            }
        }

        /// <summary>
        /// 执行存储过程，返回查询的记录集
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>MySqlDataReader</returns>
        public static MySqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(storedProcName, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd = LoadParmaters(cmd, parameters);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                cmd.Dispose();
                parameters = null;
                return reader;
            }
        }

        public static DataSet RunProcedureDataSet(string storedProcName, MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(storedProcName, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd = LoadParmaters(cmd, parameters);
                DataSet dt = new DataSet();

                MySqlDataAdapter apa = new MySqlDataAdapter(cmd);
                apa.Fill(dt, "table1");
                cmd.Parameters.Clear();
                cmd.Dispose();
                connection.Close();
                parameters = null;
                return dt;
            }
        }

        /// <summary>
        /// 执行存储过程，返回 新插入行的最后标识
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns>MySqlDataReader</returns>
        public static string RunProcedureTag(string storedProcName, IDataParameter[] parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(storedProcName, connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd = LoadParmaters(cmd, parameters);
                string reader = Convert.ToString(cmd.ExecuteScalar());
                cmd.Parameters.Clear();
                cmd.Dispose();
                parameters = null;
                return reader;
            }
        }
        #endregion



        #region .      返回记录集      .
        /// <summary>
        /// 执行SQL语句，并返回DataReader记录集
        /// </summary>
        /// <param name="SQLText">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>MySqlDataReader</returns>
        public static MySqlDataReader ExecuteDataReader(string SQLText, MySqlParameter[] parameters)
        {

            try
            {
                MySqlConnection connection = GetConnection();

                MySqlCommand cmd = new MySqlCommand(SQLText, connection);
                cmd = LoadParmaters(cmd, parameters);

                MySqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Dispose();
                cmd.Parameters.Clear();
                return dataReader;

            }
            catch (Exception Error)
            {
                throw ThrowSQLError(Error, SQLText);
            }
        }


        /// <summary>
        /// 执行SQL语句，并返回DataSet对象
        /// </summary>
        /// <param name="SQLText">SQL语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteDataSet(string SQLText, MySqlParameter[] parameters)
        {
            try
            {
                using (MySqlConnection connection = GetConnection())
                {
                    MySqlCommand cmd = new MySqlCommand(SQLText, connection);
                    cmd = LoadParmaters(cmd, parameters);
                    MySqlDataAdapter apa = new MySqlDataAdapter(cmd);
                    DataSet dt = new DataSet();
                    apa.Fill(dt, "table1");
                    cmd.Parameters.Clear();
                    cmd.Dispose();
                    parameters = null;
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception Error)
            {
                throw ThrowSQLError(Error, SQLText);
            }
        }






        #endregion
    }
}
